package com.rychlickikicior.kitijdb;

/*
 Copyright (c) 2010 Krzysztof Rychlicki-Kicior

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
 */

import java.io.FileInputStream;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
/**
 * Main database manager class.
 * That class is used to perform operations in the database.
 * @author kitikat
 */
public class DBManager {
    private QueryData queryData;
    private Properties properties;
    private Connection connection;
    private boolean underscore = false;
    private int batchSize = 5000;
    private ReflectionHandler reflectionHandler = new ReflectionHandler();

    private static DBManager _instance;

    private DBManager() {}

    private void connect() throws SQLException, ClassNotFoundException {
        String url = properties.getProperty("jdbcurl").toString();
        Class.forName(properties.getProperty("driverclass").toString());
        if (properties.contains("batchsize"))
            this.batchSize = Integer.parseInt(properties.getProperty("batchsize"));
        this.underscore = properties.containsKey("underscore");
        reflectionHandler.setUnderscore(this.underscore);
        this.connection = DriverManager.getConnection(url);
    }

    /**
     * Selects list of entities with its child collections.
     * The query content must conform to the format: entityTablename_childTablename.
     * @param <T> Entity class type
     * @param type Entity class object
     * @param queryName Name of the query used in the settings file.
     * @param params Parameters used in the given query.
     * @return List of query results.
     */
    public <T> List<T> selectRelated(Class<T> type, String queryName, Object ... params) {
        Descriptor descriptor = this.queryData.get(type);
        Query q = descriptor.findQuery(queryName, QueryType.SELECT);
        try {
            int counter = 0;
            for (String param : q.getParameterNames()) {
                q.setParameter(param, params[counter]);
                counter++;
            }
            PreparedStatement statement = this.connection.prepareStatement(q.prepare());
            q.fillValues(statement);
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            List<T> results = new ArrayList<T>();
            while (resultSet.next()) {
                T result = type.newInstance();
                Map<String, Object> childrenOfEntity = new HashMap<String, Object>();
                for (int i=0;i<metaData.getColumnCount();i++) {
                    String columnName = metaData.getColumnLabel(i+1);
                    String[] fieldParts = columnName.split("__");
                    if (fieldParts.length == 1) {
                        Method setter = reflectionHandler.getBeanSetMethod(type, columnName);
                        setter.invoke(result, resultSet.getObject(i+1));
                    } else {
                        String table = fieldParts[0];
                        String childColumnName = fieldParts[1];
                        Class elementClass = descriptor.getClassByValue(table);
                        if (elementClass != null) {
                            Object happyChild = childrenOfEntity.get(table);
                            if (happyChild == null) {
                                happyChild = elementClass.newInstance();
                                childrenOfEntity.put(table, happyChild);
                            }
                            Method setter = reflectionHandler.getBeanSetMethod(elementClass, childColumnName);
                            setter.invoke(happyChild, resultSet.getObject(i+1));
                        }
                    }
                }
                if (results.contains(result)) {
                    T original = results.get(results.indexOf(result));
                    for (Class childClass : descriptor.getChildClasses()) {
                        String propertyName = descriptor.getChild(childClass);
                        Method getter = reflectionHandler.getBeanReadMethod(type, propertyName);
                        Collection children = (Collection)getter.invoke(original);
                        Object toAdd = childrenOfEntity.get(propertyName);
                        if (!children.contains(toAdd)) 
                            children.add(toAdd);
                    }
                } else {
                    for (Class childClass : descriptor.getChildClasses()) {
                        String propertyName = descriptor.getChild(childClass);
                        Method setter = reflectionHandler.getBeanSetMethod(type, propertyName);
                        Collection c = (Collection)descriptor.getPropertyType(childClass).newInstance();
                        c.add(childrenOfEntity.get(propertyName));
                        setter.invoke(result, c);
                    }
                    results.add(result);
                }
            }
            return results;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Method finds and returns a single object of given type, using number unique identifier.
     * Student s = manager.select(Student.class, 20);
     * This method can be used if the primary key of the entity is defined as integer.
     * @param <T> The type of the entity to fetch.
     * @param type The entity class
     * @param key Value of the primary key.
     * @return Entity object (null, if object with given key cannot be found)
     */
    public <T> T select(Class<T> type, int key) {
        return this.select(type, new PrimaryKey(key));
    }

    /**
     * Method finds and returns a single object of given type, using given primary key.
     * Student s = manager.select(Student.class, new PrimaryKey(20));
     * This method can be used with primary key consisting of multiple fields.
     * @param <T> The type of the entity to fetch.
     * @param type The entity class
     * @param key Value of the primary key.
     * @return Entity object (null, if object with given key cannot be found)
     */
    public <T> T select(Class<T> type, PrimaryKey key) {
        List<T> list = this.select(type, "default", key.getKey());
        if (list == null || list.size() == 0)
            return null;
        else
            return list.get(0);
    }

    /**
     * Returns a list of entities, fetched as a result of given query, optionally using given parameters.
     * @param <T> The type of the entity to fetch.
     * @param type The entity class
     * @param queryName Name of the query, as described in the XML configuration file
     * @param params Optional query parameters
     * @return The list of found entities.
     */
    public <T> List<T> select(Class<T> type, String queryName, Object ... params) {
        Descriptor descriptor = this.queryData.get(type);
        Query q = descriptor.findQuery(queryName, QueryType.SELECT);
        try {
            int counter = 0;
            for (String param : q.getParameterNames()) {
                q.setParameter(param, params[counter]);
                counter++;
            }
            PreparedStatement statement = this.connection.prepareStatement(q.prepare());
            q.fillValues(statement);
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            List<T> results = new ArrayList<T>();
            while (resultSet.next()) {
                T result = type.newInstance();
                for (int i=0; i<metaData.getColumnCount(); i++) {
                    String columnName = metaData.getColumnName(i+1);
                    Method setter = reflectionHandler.getBeanSetMethod(type, columnName);
                    setter.invoke(result, resultSet.getObject(i+1));
                }
                results.add(result);
            }
            return results;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * Selects various results for the given query
     * @param c The class, in which the query has been defined in the settings file.
     * @param queryName Name of the query, in the given class.
     * @param params Query parameters.
     * @return One-dimensional values' (fields') list, if there is only one record in results, list of lists otherwise.
     */
    public List selectGeneric(Class c, String queryName, Object ... params) {
        Descriptor descriptor = this.queryData.get(c);
        Query q = descriptor.findQuery(queryName, QueryType.SELECT);
        int counter = 0;
        for (String param : q.getParameterNames()) {
            q.setParameter(param, params[counter]);
            counter++;
        }
        List result = new ArrayList();
        try {
            PreparedStatement statement = this.connection.prepareStatement(q.prepare());
            q.fillValues(statement);
            ResultSet resultSet = statement.executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            if (resultSet.last()) {
                if (resultSet.getRow()==1) { // pierwszy wiersz jest ostatnim, zatem mamy jeden wynik
                    for (int i=0; i<metaData.getColumnCount(); i++)
                        result.add(resultSet.getObject(i+1));
                    return result;
                } else {
                    resultSet.first();
                    do {
                        List record = new ArrayList();
                        for (int i=0; i<metaData.getColumnCount(); i++) 
                            record.add(resultSet.getObject(i+1));
                        result.add(record);
                    } while (resultSet.next());
                    return result;
                }
            }
            else
                return result;
        } catch (SQLException se) { se.printStackTrace(); }
        return result;
    }
    /**
     * Puts the entity object into the database.
     * @param entity Entity object to be inserted. If you expect the auto-generated ID value to be set, ID field should be set to null before calling this method.
     * @return The entity object with generated ID value.
     */
    public Object insert(Object entity) {
        Class c = entity.getClass();
        LinkedList<Class> list = new LinkedList<Class>();
        list.add(c);
        while ((c = c.getSuperclass()) != null && c != Object.class)
            list.add(c);
        Class ultimateParent = list.removeLast();
        PrimaryKey keyToUse = this.insert(entity, ultimateParent);
        if (keyToUse == null)
            return null;
        while (!list.isEmpty()) {
            c = list.removeLast();
            Object[] values = this.getValuesOfClass(entity, c);
            List args = new ArrayList();
            args.addAll(Arrays.asList(keyToUse.getKey()));
            args.addAll(Arrays.asList(values));
            if (this.modify(c, QueryType.INSERT, "default", args.toArray())<=0) {
                this.delete(ultimateParent, keyToUse);
                return null;
            }
        }
        if (keyToUse != null)
            return this.select(entity.getClass(), keyToUse);
        else
            return entity;
    }

    private Object[] getValuesOfClass(Object entity, Class c) {
        List list = new ArrayList();
        for (Method m : c.getDeclaredMethods()) {
            if (m.getName().startsWith("get")) {
                try {
                    list.add(m.invoke(entity));
                } catch (Exception iae) {iae.printStackTrace();}
            }
        }
        return list.toArray();
    }

    private PrimaryKey insert(Object entity, Class entityClass) {
        Descriptor descriptor = this.queryData.get(entityClass);
        Query q = descriptor.findQuery("default", QueryType.INSERT);
        try {
            for (String param : q.getParameterNames()) {
                Method getter = reflectionHandler.getBeanReadMethod(entityClass, param);
                q.setParameter(param, getter.invoke(entity));
            }
            PreparedStatement statement = this.connection.prepareStatement(q.prepare(), Statement.RETURN_GENERATED_KEYS);
            q.fillValues(statement);
            int result = statement.executeUpdate();
            ResultSet rs = statement.getGeneratedKeys();
            ResultSetMetaData metaData = rs.getMetaData();
            PrimaryKey keyOut = null;
            if (rs.next()) { // move to record of added key
                    Object[] key = new Object[metaData.getColumnCount()];
                    for (int i=0;i<metaData.getColumnCount();i++)
                        key[i] = rs.getObject(i+1);
                    keyOut = new PrimaryKey();
                    keyOut.setKey(key);
            } else 
                keyOut = this.resolvePrimaryKey(entity);
            return keyOut;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * Updates entity state in the database.
     * @param entity The entity object.
     * @return Number of records, which have been modified during execution of associated query (when multitable entity is modified, it is greater than 1).
     */
    public int update(Object entity) {
        return this.update(entity, true);
    }
    private int update(Object entity, boolean doTransaction) {
        Class basicClass = entity.getClass();
        Descriptor d = this.queryData.get(basicClass);
        int suma = 0;
        try {
            LinkedList<Class> list = new LinkedList<Class>();
            list.add(basicClass);
            Class c = basicClass;
            while ((c = c.getSuperclass()) != null && c != Object.class)
                list.add(c);
            Descriptor eldest = this.queryData.get(list.get(list.size()-1));
            if (doTransaction && eldest.isVersion()) {
                this.connection.setAutoCommit(false);
                PrimaryKey key = this.resolvePrimaryKey(entity);
                Object o = this.select(basicClass, key);
                int verNew = this.resolveVersion(o);
                int verOld = this.resolveVersion(entity);
                if (verNew != verOld) {
                    this.connection.setAutoCommit(true);
                    return 0;
                }
                this.incrementVersion(entity, verOld+1);
            }
            
            while (!list.isEmpty()) {
                c = list.removeLast();
                suma += this.update(entity, c);
            }
            if (doTransaction && eldest.isVersion()) {
                this.connection.commit();
                this.connection.setAutoCommit(true);
            }
        } catch (SQLException sqle) { sqle.printStackTrace(); }
        return suma;
    }

    private PrimaryKey resolvePrimaryKey(Object entity) {
        Class c = entity.getClass();
        Descriptor d = this.queryData.get(c);
        Query q = d.findQuery("default", QueryType.SELECT);
        Collection<String> params = q.getParameterNames();
        List values = new ArrayList();
        for (String s : params) {
            Method m = reflectionHandler.getBeanReadMethod(c, s);
            try {
                values.add(m.invoke(entity));
            } catch (Exception e) { e.printStackTrace(); }
        }
        PrimaryKey result = new PrimaryKey(values.toArray());
        return result;
    }

    private int resolveVersion(Object o) {
        try {
            Method m = o.getClass().getMethod("getDbversion");
            return Integer.parseInt(m.invoke(o).toString());
        } catch (Exception e) { e.printStackTrace(); }
        return 0;
    }

    private void incrementVersion(Object o, Integer value) {
        try {
            Method m = reflectionHandler.getBeanSetMethod(o.getClass(), "dbversion");
            m.invoke(o, value);
        } catch (Exception e) { e.printStackTrace(); }
    }

    private int update(Object entity, Class entityClass) {
        Descriptor descriptor = this.queryData.get(entityClass);
        Query q = descriptor.findQuery("default", QueryType.UPDATE);
        if (q != null) {
            try {
                for (String param : q.getParameterNames()) {
                    Method getter = reflectionHandler.getBeanReadMethod(entityClass, param);
                    q.setParameter(param, getter.invoke(entity));
                }
                PreparedStatement statement = this.connection.prepareStatement(q.prepare());
                q.fillValues(statement);
                return statement.executeUpdate();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    /**
     * Performs any modifying query (INSERT/UPDATE/DELETE). The query must belong to one of the entity classes (in settings file).
     * @param type Entity class, in which the query has been defined.
     * @param queryType Query type(INSERT, DELETE or UPDATE)
     * @param queryName Name of the query in the settings file.
     * @param params Parameters of the query (optional).
     * @return Number of modified records.
     */
    public int modify(Class type, QueryType queryType, String queryName, Object ... params) {
        Descriptor descriptor = this.queryData.get(type);
        Query q = descriptor.findQuery(queryName, queryType);
        try {
            int counter = 0;
            for (String param : q.getParameterNames()) {
                q.setParameter(param, params[counter]);
                counter++;
            }
            PreparedStatement statement = this.connection.prepareStatement(q.prepare());
            q.fillValues(statement);
            return statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Integer.MIN_VALUE;
    }
   /**
     * Removes the entity object from the database
     * In multitable entity classes, this method removes only the record of the base entity class.
     * Metode te mozna stosowac tylko dla tabel zawierajacych klucz glowny skladajacy sie z jednej liczby calkowitej.
     * @param <T> Type of the entity.
     * @param type Entity class.
     * @param key Value of the primary key used to find and delete the entity object.
     * @return Number of the modified records.
     */
    public int delete(Class type, PrimaryKey key) {
        Class c = type;
        while (c != null && c.getSuperclass() != Object.class)
            c = c.getSuperclass();
        Descriptor descriptor = this.queryData.get(c);
        Query q = descriptor.findQuery("default", QueryType.DELETE);
        try {
            Object[] keyValues = key.getKey();
            int counter = 0;
            for (String param : q.getParameterNames()) {
                q.setParameter(param, keyValues[counter]);
                counter++;
            }
            PreparedStatement statement = this.connection.prepareStatement(q.prepare());
            q.fillValues(statement);
            return statement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }

    /**
     * Removes the entity object from the database
     * In multitable entity classes, this method removes only the record of the base entity class.
     * Metode te mozna stosowac tylko dla tabel zawierajacych klucz glowny skladajacy sie z jednej liczby calkowitej.
     * @param <T> Type of the entity.
     * @param type Entity class.
     * @param key Value of the primary key (the ol', plain, single-integer-primary-key case).
     * @return Number of the modified records.
     */
    public <T> int delete(Class<T> type, int key) {
        return this.delete(type, new PrimaryKey(key));
    }
    /**
     * Creates the DBManager or returns the one already created.
     * @param is Stream object used to read settings data.
     * @return Created manager instance.
     */
    public synchronized static DBManager createManager(InputStream is) {
        if (_instance != null)
            return _instance;
        else {
            try {
                //FileInputStream fis = new FileInputStream(fileName);
                SettingsReader reader = SettingsReader.createReader(SettingsReader.SettingsType.XML, is);
                DBManager dbManager = new DBManager();
                dbManager.setProperties(reader.getProperties());
                dbManager.setQueryData(reader.getQueryData());
                dbManager.connect();
                _instance = dbManager;
                return _instance;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * Creates the DBManager or returns the one already created.
     * @param  Name of the file containing the settings data.
     * @return Created manager instance.
     */
    public synchronized static DBManager createManager(String fileName) {
         if (_instance != null)
            return _instance;
        try {
            FileInputStream fis = new FileInputStream(fileName);
            DBManager manager = createManager(fis);
            fis.close();
            return manager;
        } catch (Exception e) { e.printStackTrace(); }
        return null;
    }

    /**
     * Creates the DBManager or returns the one already created, applying given JDBC URL to the manager.
     * @param is Stream object used to read settings data.
     * @param jdbcUrl The string containing valid JDBC URL, applied to the manager instance, even it has not been created during given call of that method.
     * @return Created manager instance.
     */
    public synchronized static DBManager createManager(InputStream is, String jdbcUrl) {
        DBManager manager = createManager(is);
        manager.getProperties().setProperty("jdbcurl", jdbcUrl);
        return manager;
    }

    /**
     * Closes the underlying JDBC connection. Should be called before application is closed.
     */
    public synchronized void close() {
        try {
            this.connection.close();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }
    /**
     * Performs group entity insert - in one transaction.
     * @param entities Collection of the entity objects. May contain entities of different types.
     * @return Number of inserted entities.
     */
    public int multiInsert(List entities) {
        int sum = 0;
        int partialSum = 0;
        try {
            this.connection.setAutoCommit(false);
            for (int i=0;i<entities.size();i++) {
                if (partialSum == this.batchSize) {
                    partialSum = 0;
                    this.connection.commit();
                }
                this.insert(entities.get(i));
                sum++;
                partialSum++;
            }
            this.connection.commit();
            this.connection.setAutoCommit(true);
        } catch (Exception e) { e.printStackTrace(); }
        return sum;
    }

    /**
     * Performs group entity update - in one transaction.
     * @param entities Collection of the entity objects. May contain entities of different types.
     * @return Number of inserted entities.
     */
    public int multiUpdate(List entities) {
        int sum = 0;
        int partialSum = 0;
        try {
            this.connection.setAutoCommit(false);
            for (int i=0;i<entities.size();i++) {
                if (partialSum == this.batchSize) {
                    partialSum = 0;
                    this.connection.commit();
                }
                this.update(entities.get(i), false);
                sum++;
                partialSum++;
            }
            this.connection.commit();
            this.connection.setAutoCommit(true);
        } catch (Exception e) { e.printStackTrace(); }
        return sum;
    }

    private QueryData getQueryData() {
        return queryData;
    }

    private void setQueryData(QueryData queryData) {
        this.queryData = queryData;
    }

    private Properties getProperties() {
        return properties;
    }

    private void setProperties(Properties properties) {
        this.properties = properties;
    }

    public boolean isUnderscore() {
        return underscore;
    }
}
